MySQL索引 |
您所在的位置：网站首页 › mysql in用法 › MySQL索引 |
MySQL索引
|
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询数据库表中数据。 索引的意义索引用于快速找出在某个字段中有特定值的行。如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行，那么表越大，查询数据所花费的时间就越多。如果在表中查询的字段有索引，MySQL能够快速到达一个位置去检索数据文件，而不需要再去查看所有数据，那么将会节省很大一部分查询时间。 比如说emp表中1W个员工的记录，要查询salesman的员工信息，如果没有索引，服务器会从表中第一条记录开始，一条条往下遍历，直到找到职位为salesman的员工信息。如果有了索引，它会把job这个字段，通过一定的方法进行存储，在查询这个字段上的信息时，能够快速找到对应的数据，而不需要再遍历1W条记录了。 索引的优缺点所有MySQL的字段类型都可以添加索引，但是索引也不是越多越好，而是要根据业务数据合理的使用。 优点 通过索引对数据进行检索，大大提高了数据的查询效率。 缺点 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。 索引也需要占空间的，创建的索引太多，索引文件也会占用数据库的存储空间。 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。 创建索引的原则需要创建索引的情况： 主外键和唯一约束的字段自动创建索引 频繁作为查询条件的字段应该创建索引 查询中排序的字段应该创建索引 查询中分组或统计的字段应该创建索引不需要创建索引的情况： 表中记录太少不需要创建索引 需要频繁增删改的字段不适合创建索引 where子句中用不到的字段不需要创建索引 重复值较多的字段不需要创建索引 索引的结构索引是在存储引擎中实现的，使用不同的存储引擎，所支持的索引也是不同的。
在mysql中常用两种索引结构BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引。 索引的类型可以分类以下几种： 普通索引：最基本的索引，没有任何限制 唯一索引：索引字段的取值不能重复，可以有空值，但空值也只能出现一次。 主键索引：索引字段的取值不能为空，也不能重复。 组合索引：一个索引包含多个字段，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 全文索引：通过关键字符，就能找到该字段所属的记录行。仅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT类型的字段上使用。 空间索引：对空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）的字段建立的索引，仅限MyISAM引擎，且要求索引字段的取值不能为空。 索引的操作实际上索引也是一张表，创建索引时，数据库管理系统会在本地磁盘建立索引文件，里面保存了索引字段，并指向实体表的记录。 创建索引创建表的同时须指定索引名、表名和字段名。语法： create index on ();自动创建索引： 在表中定义了主键约束时，会自动创建一个对应的主键索引。 在表中定义了外键约束时，会自动创建一个对应的普通索引。 在表中定义了唯一约束时，会自动创建一个对应的唯一索引。示例：emp表中的job添加普通索引 mysql> create index job_index on emp(job); 查看索引：语法： show index from ;示例：查看emp表中的索引 mysql> show index from emp; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | YES | NULL | | emp | 0 | ename | 1 | ename | A | 14 | NULL | NULL | YES | BTREE | | | YES | NULL | | emp | 1 | deptno | 1 | deptno | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL | | emp | 1 | job_index | 1 | job | A | 5 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 使用索引在查询语句中使用索引会大大提升数据的检索速度。示例： mysql> select ename,job,deptno from emp where job='salesman'; +--------+----------+--------+ | ename | job | deptno | +--------+----------+--------+ | allen | salesman | 30 | | ward | salesman | 30 | | martin | salesman | 30 | | turner | salesman | 30 | +--------+----------+--------+ 删除索引删除索引只是删除了表中的索引对象，表中的数据不会被删除。语法： drop index on ;示例： mysql> drop index job_index on emp; mysql> select ename,job,deptno from emp where job='salesman'; +--------+----------+--------+ | ename | job | deptno | +--------+----------+--------+ | allen | salesman | 30 | | ward | salesman | 30 | | martin | salesman | 30 | | turner | salesman | 30 | +--------+----------+--------+ |
MySQL的InnoDB存储引擎是支持hash索引的，不过我们必须启用，hash索引的创建由InnoDB存储引擎自动优化创建，我们干预不了。【本文地址】